package com.sisi.web;

import entity.Student813;
import entity.StudentBanTea813;
import util.JavaUtil;
import util.PageInfo;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@WebServlet("/student813")
public class Student813Servlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String method = req.getParameter("method");
        if (method == null || method.equals("")){
            method = "selectByPage";
        }
        switch (method){
            case "selectByPage" :
                selectByPage(req,resp);
                break;
            case "deleteById" :
                deleteById(req,resp);
                break;
            case "selectById" :
                selectById(req,resp);
                break;
            case "insert" :
                insert(req,resp);
                break;
            case "update" :
                update(req,resp);
                break;
        }
    }

    private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JavaUtil.getConnection();
            String sql = "DELETE FROM student813 WHERE id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            int count = statement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JavaUtil.close(connection,statement,null);
        }
        resp.sendRedirect(req.getContextPath() + "/student813?method=selectByPage");

    }

    private void selectById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("selectById");
        int id = Integer.parseInt(req.getParameter("id"));
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Student813 student813 = null;
        try {
            connection = JavaUtil.getConnection();
            String sql = "SELECT name,age,gender,teacher812_id,banji813_id FROM student813 WHERE id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            System.out.println(statement);
            resultSet =statement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                int teacher812_id = resultSet.getInt("teacher812_id");
                int banji813_id = resultSet.getInt("banji813_id");
                student813 = new Student813(id, name, age, gender,teacher812_id,banji813_id);
            }
            System.out.println(student813);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JavaUtil.close(connection,statement,resultSet);
        }
        req.setAttribute("st813",student813);
        req.getRequestDispatcher("/student813_update.jsp").forward(req,resp);

    }

    private void insert(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        System.out.println("insert");
        String name = req.getParameter("studentName");
        int age = Integer.parseInt(req.getParameter("studentAge"));
        String gender = req.getParameter("studentGender");
        int banJiId = Integer.parseInt(req.getParameter("banJiId"));
        int teacherId = Integer.parseInt(req.getParameter("teacherId"));
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JavaUtil.getConnection();
            String sql = "INSERT INTO student813(name,age,gender,teacher812_id,banji813_id) VALUES(?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,age);
            statement.setString(3,gender);
            statement.setInt(4,banJiId);
            statement.setInt(5,teacherId);
            int count = statement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JavaUtil.close(connection,statement,null);
        }
        resp.sendRedirect(req.getContextPath() + "/student813?method=selectByPage");
    }

    private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        System.out.println("update813");
        int id = Integer.parseInt(req.getParameter("id"));
        String name = req.getParameter("name");
        int age = Integer.parseInt(req.getParameter("age"));
        String gender = req.getParameter("gender");
        int banJiId = Integer.parseInt(req.getParameter("banJiId"));
        int teacherId = Integer.parseInt(req.getParameter("teacherId"));
        System.out.println("数据传输完成");
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JavaUtil.getConnection();
            String sql = "UPDATE student813 SET name=?,age=?,gender=?,teacher812_id=?,banji813_id=? WHERE id=?";
            System.out.println(sql);
            statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,age);
            statement.setString(3,gender);
            statement.setInt(4,teacherId);
            statement.setInt(5,banJiId);
            statement.setInt(6,id);
            System.out.println(statement);
            int count = statement.executeUpdate();
            System.out.println(count);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JavaUtil.close(connection,statement,null);
        }
        resp.sendRedirect(req.getContextPath() + "/student813?method=selectByPage");

    }

    private void selectByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        System.out.println("selectByPages813");
        String pageNoStr = req.getParameter("pageNo");
        if (pageNoStr == null || pageNoStr.equals("")){
            pageNoStr = "1";
        }
        int pageNo = Integer.parseInt(pageNoStr);
        String pageSizeStr = req.getParameter("pageSize");
        if (pageSizeStr == null || pageSizeStr.equals("")){
            pageSizeStr = "5";
        }
        int pageSize = Integer.parseInt(pageSizeStr);
        int offset = (pageNo - 1) * pageSize;
        ArrayList<StudentBanTea813> arrayList = getCurrentPage(offset,pageSize);
        int totalCount = getTotalCount();
        int totalPage = (int) Math.ceil((double) totalCount / pageSize);
        PageInfo pageInfo = new PageInfo(arrayList,pageNo,totalPage,pageSize);
        req.setAttribute("page",pageInfo);
        req.getRequestDispatcher("/student813_list.jsp").forward(req,resp);
    }

    private int getTotalCount() {
        System.out.println("getTotalCount");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int totalCount = 0;
        try {
            connection = JavaUtil.getConnection();
            String sql = "SELECT count(*) FROM student813";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JavaUtil.close(connection,statement,resultSet);
        }
        return totalCount;
    }

    private ArrayList<StudentBanTea813> getCurrentPage(int offset, int pageSize) {
        System.out.println("getPage");
        Connection connection = null;
        ArrayList<StudentBanTea813> arrayList = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JavaUtil.getConnection();
            String sql = "SELECT st.id AS studentId,st.`name` AS studentName,st.age AS studentAge,st.gender AS studentGender,bj.`name` AS banJiName,te.teacherName AS teacherName\n" +
                    "FROM student813 AS st INNER JOIN banJi813 AS bj ON st.banji813_id = bj.id\n" +
                    "INNER JOIN teacher812 AS te ON st.teacher812_id = te.teacherId LIMIT ?,?";
            System.out.println(sql);
            statement = connection.prepareStatement(sql);
            System.out.println(statement);
            statement.setInt(1,offset);
            statement.setInt(2,pageSize);
            System.out.println(statement);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                System.out.println("输出进数组");
                int id = resultSet.getInt("studentId");
                String studentName = resultSet.getString("studentName");
                int age = resultSet.getInt("studentAge");
                String gender = resultSet.getString("studentGender");
                String banJiName = resultSet.getString("banJiName");
                String teacherName = resultSet.getString("teacherName");
                StudentBanTea813 studentBanTea813 = new StudentBanTea813(id,studentName,age,gender,banJiName,teacherName);
                System.out.println(studentBanTea813);
                arrayList.add(studentBanTea813);
                System.out.println(arrayList);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JavaUtil.close(connection,statement,resultSet);
        }
        return arrayList;
    }
}
